# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：     test
   Description :
   Author :       Administrator
   date：          2019/4/3
-------------------------------------------------
   Change Activity:
                   2019/4/3:
-------------------------------------------------
"""
__author__ = 'Administrator'

import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import datetime as dt
from sqlalchemy import types
import re

con = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/BP_New_Factor')
conTL = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/TLDB')

Today = dt.datetime.strftime(dt.datetime.today(), '%Y-%m-%d')
TLDB_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM TLDB.dbo.mkt_equd \
                               ORDER BY TRADE_DATE DESC', conTL).iloc[0, 0]
Factor_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA005 \
                               ORDER BY TRADE_DATE DESC', con).iloc[0, 0]
Factor_230_Date = pd.read_sql('SELECT TOP (250) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA005 \
                               ORDER BY TICKER_SYMBOL,TRADE_DATE DESC', con).iloc[-1, 0]


# %% 定义基本函数

def rolling_corr(df1, df2, window):
    if df1.shape[0] != df2.shape[0]:
        df1 = df1.reindex(df2.index)
    return df1.rolling(window).corr(df2)


def rolling_rank(df1, window, percent=True):
    rank_list = []
    for start in range(df1.shape[0] - window + 1):
        end = start + window
        x = df1.iloc[start:end]
        rank = x.rank(axis=0, pct=percent).iloc[-1]
        rank_list.append(rank)
    rank_df = pd.concat(rank_list, axis=1).T.set_index(df1.index[window - 1:])
    return rank_df


def scale(df, n=1):
    return n * df.div(abs(df).sum(axis=1), axis=0)


def decay_linear(df, window):
    weight = np.array(range(1, window + 1))
    result_list = []
    for start in range(df.shape[0] - window + 1):
        end = start + window
        x = df.values[start:end]
        weighted_mean = np.average(x, axis=0, weights=weight)
        result_list.append(weighted_mean)
    result_df = pd.DataFrame(result_list, columns=df.columns).set_index(df.index[window - 1:])
    return result_df


# %% 获取后复权数据（包含vwap，2009年开始）
vmarket_af = pd.read_sql("SELECT a.TRADE_DATE, a.TICKER_SYMBOL, \
                   a.OPEN_PRICE_2 as 'open', a.HIGHEST_PRICE_2 as high, \
                   a.LOWEST_PRICE_2 as low, a.CLOSE_PRICE_2 as 'close', \
                   (b.TURNOVER_VOL / a.ACCUM_ADJ_FACTOR_2) as volume, \
                   b.NEG_MARKET_VALUE as nmkt, (v.VWAP * a.ACCUM_ADJ_FACTOR_2) as vwap \
                   FROM TLDB.dbo.mkt_equd_adj_af AS a, TLDB.dbo.mkt_equd AS b, TLDB.dbo.mkt_equ_vwapd as v \
                   WHERE a.SECURITY_ID = b.SECURITY_ID \
                   and a.SECURITY_ID = v.SECURITY_ID \
                   and a.TRADE_DATE = b.TRADE_DATE \
                   and a.TRADE_DATE = v.TRADE_DATE \
                   and a.TRADE_DATE > '%s' \
                   and a.TRADE_DATE <= '%s' \
                   ORDER by TICKER_SYMBOL, TRADE_DATE" % (Factor_230_Date, TLDB_Latest_Date), conTL, index_col=['TRADE_DATE', 'TICKER_SYMBOL'])

vopen_af = vmarket_af['open'].unstack()
vclose_af = vmarket_af['close'].unstack()
vhigh_af = vmarket_af['high'].unstack()
vlow_af = vmarket_af['low'].unstack()
vvolume_af = vmarket_af['volume'].unstack()
vnmkt = vmarket_af['nmkt'].unstack()
vwap_af = vmarket_af['vwap'].unstack()
vret = vclose_af.pct_change()

# %%
print(dt.datetime.now())
# alpha005:
# (rank((open - (sum(vwap, 10) / 10))) * (-1 * abs(rank((close - vwap)))))
a = (vopen_af - vwap_af.rolling(10).mean()).rank(axis=1, pct=True)
b = -abs((vclose_af - vwap_af).rank(axis=1, pct=True))
ALPHA005 = a * b

# alpha011:
# ((rank(ts_max((vwap - close), 3)) + rank(ts_min((vwap - close), 3))) *rank(delta(volume, 3)))
a = (vwap_af - vclose_af).rolling(3).max().rank(axis=1, pct=True)
b = (vwap_af - vclose_af).rolling(3).min().rank(axis=1, pct=True)
c = (vvolume_af - vvolume_af.shift(3)).rank(axis=1, pct=True)
ALPHA011 = a + b + c

# alpha025:
# rank(((((-1 * returns) * adv20) * vwap) * (high - close)))
adv20 = vvolume_af.rolling(20).mean()
ALPHA025 = (-vret * adv20 * vwap_af * (vhigh_af - vclose_af)).rank(axis=1, pct=True)

# alpha027:
# ((0.5 < rank((sum(correlation(rank(volume), rank(vwap), 6), 2) / 2.0))) ? (-1 * 1): 1)
a = vvolume_af.rank(axis=1, pct=True)
b = vwap_af.rank(axis=1, pct=True)
x = rolling_corr(a, b, 6).rolling(2).mean().rank(axis=1, pct=True)
e = pd.DataFrame(np.ones_like(vclose_af), index=vclose_af.index, columns=vclose_af.columns)
ALPHA027 = e.where(x <= 0.5, -1)

# alpha032:
# (scale(((sum(close, 7) / 7) - close)) + (20 * scale(correlation(vwap, delay(close, 5),230))))
a = scale(vclose_af.rolling(7).mean() - vclose_af)
b = scale(rolling_corr(vwap_af, vclose_af.shift(5), 230))
ALPHA032 = a + b

# alpha036:
# (((((2.21 * rank(correlation((close - open), delay(volume, 1), 15))) + (0.7 * rank((open- close)))) + (0.73 * rank(Ts_Rank(delay((-1 * returns), 6), 5)))) + rank(abs(correlation(vwap,adv20, 6)))) + (0.6 * rank((((sum(close, 200) / 200) - open) * (close - open)))))
adv20 = vvolume_af.rolling(20).mean()
a = 2.21 * rolling_corr(vclose_af - vopen_af, vvolume_af - vvolume_af.shift(), 15).rank(axis=1, pct=True)
b = 0.7 * (vopen_af - vclose_af)
c = 0.73 * rolling_rank(vret.shift(6) - vret, 5).rank(axis=1, pct=True)
d = abs(rolling_corr(vwap_af, adv20, 6)).rank(axis=1, pct=True)
e = 0.6 * ((vclose_af.rolling(200).mean() - vopen_af) * (vclose_af - vopen_af)).rank(axis=1, pct=True)
ALPHA036 = a + b + c + d + e

# alpha041:
# (((high * low)^0.5) - vwap)
ALPHA041 = (vhigh_af * vlow_af) ** 0.5 - vwap_af

# alpha042:
# (rank((vwap - close)) / rank((vwap + close)))
a = (vwap_af - vclose_af).rank(axis=1, pct=True)
b = (vwap_af + vclose_af).rank(axis=1, pct=True)
ALPHA042 = a / b

# alpha047:
# ((((rank((1 / close)) * volume) / adv20) * ((high * rank((high - close))) / (sum(high, 5) /5))) - rank((vwap - delay(vwap, 5))))
adv20 = vvolume_af.rolling(20).mean()
a = (1 / vclose_af).rank(axis=1, pct=True) * vvolume_af / adv20
b = vhigh_af * (vhigh_af - vclose_af).rank(axis=1, pct=True) / vhigh_af.rolling(5).mean()
c = (vwap_af - vwap_af.shift(5)).rank(axis=1, pct=True)
ALPHA047 = a * b - c

# alpha050:
# (-1 * ts_max(rank(correlation(rank(volume), rank(vwap), 5)), 5))
a = vvolume_af.rank(axis=1, pct=True)
b = vwap_af.rank(axis=1, pct=True)
ALPHA050 = -(rolling_corr(a, b, 5).rank(axis=1, pct=True)).rolling(5).max()

# alpha057:
# (0 - (1 * ((close - vwap) / decay_linear(rank(ts_argmax(close, 30)), 2))))
a = vclose_af - vwap_af
b = vclose_af.rolling(30).apply(np.nanargmax).rank(axis=1, pct=True)
ALPHA057 = -a / decay_linear(b, 2)

# alpha061:
# (rank((vwap_af - ts_min(vwap_af, 16.1219))) < rank(correlation(vwap_af, adv180, 17.9282)))
adv180 = vvolume_af.rolling(180).mean()
a = vwap_af - vwap_af.rolling(16).min()
x = a.rank(axis=1, pct=True)
b = rolling_corr(vwap_af, adv180, 17)
y = b.rank(axis=1, pct=True)
ALPHA061 = 1 * (x < y).where(~x.isna())

# alpha062:
# ((rank(correlation(vwap, sum(adv20, 22.4101), 9.91009)) < rank(((rank(open) +rank(open)) < (rank(((high + low) / 2)) + rank(high))))) * -1)
adv20 = vvolume_af.rolling(20).mean()
a = rolling_corr(vwap_af, adv20.rolling(22).sum(), 9).rank(axis=1, pct=True)
b = vopen_af.rank(axis=1, pct=True)
c = ((vhigh_af + vlow_af) / 2).rank(axis=1, pct=True) + vhigh_af.rank(axis=1, pct=True)
ALPHA062 = -1 * ((b > a) & (b < c)).where(~a.isna())

# alpha063:
# alpha064:
# ((rank(correlation(sum(((open * 0.178404) + (low * (1 - 0.178404))), 12.7054),sum(adv120, 12.7054), 16.6208)) < rank(delta(((((high + low) / 2) * 0.178404) + (vwap * (1 -0.178404))), 3.69741))) * -1)
adv120 = vvolume_af.rolling(120).mean()
a = 0.178704 * vopen_af + (1 - 0.178404) * vlow_af
b = adv120.rolling(12).sum()
c = (vhigh_af + vlow_af) / 2 * 0.178404 + vwap_af * (1 - 0.178404)
x = rolling_corr(a.rolling(12).sum(), b, 16).rank(axis=1, pct=True)
y = (c - c.shift(3)).rank(axis=1, pct=True)
ALPHA064 = -1 * (x < y).where(~x.isna())

# alpha065:
# ((rank(correlation(((open * 0.00817205) + (vwap * (1 - 0.00817205))), sum(adv60,8.6911), 6.40374)) < rank((open - ts_min(open, 13.635)))) * -1)
adv60 = vvolume_af.rolling(60).mean()
a = vopen_af * 0.00817205 + vwap_af * (1 - 0.00817205)
b = adv60.rolling(8).sum()
x = rolling_corr(a, b, 6).rank(axis=1, pct=True)
c = vopen_af - vopen_af.rolling(13).min()
y = c.rank(axis=1, pct=True)
ALPHA065 = -1 * (x < y).where(~x.isna())

# alpha066:
# ((rank(decay_linear(delta(vwap, 3.51013), 7.23052)) + Ts_Rank(decay_linear(((((low* 0.96633) + (low * (1 - 0.96633))) - vwap) / (open - ((high + low) / 2))), 11.4157), 6.72611)) * -1)
a = vwap_af - vwap_af.shift(3)
x = decay_linear(a, 7).rank(axis=1, pct=True)
b = (vlow_af - vwap_af) / (vopen_af - (vhigh_af - vlow_af) / 2)
y = rolling_rank(decay_linear(b, 11), 6)
ALPHA066 = -(x + y)

# alpha069:
# alpha070:
# alpha071:
# max(Ts_Rank(decay_linear(correlation(Ts_Rank(close, 3.43976), Ts_Rank(adv180,12.0647), 18.0175), 4.20501), 15.6948), Ts_Rank(decay_linear((rank(((low + open) - (vwap +vwap)))^2), 16.4662), 4.4388))
adv180 = vvolume_af.rolling(180).mean()
a = rolling_rank(vclose_af, 3)
b = rolling_rank(adv180, 12)
x = rolling_rank(decay_linear(rolling_corr(a, b, 18), 4), 15)
c = (vlow_af + vopen_af - 2 * vwap_af).rank(axis=1, pct=True)
y = rolling_rank(decay_linear(c ** 2, 16), 4).reindex(x.index)
ALPHA071 = x.where(x > y, y)

# alpha072:
# (rank(decay_linear(correlation(((high + low) / 2), adv40, 8.93345), 10.1519)) /rank(decay_linear(correlation(Ts_Rank(vwap, 3.72469), Ts_Rank(volume, 18.5188), 6.86671),2.95011)))
adv40 = vvolume_af.rolling(40).mean()
a = rolling_corr((vhigh_af + vlow_af) / 2, adv40, 8)
x = decay_linear(a, 10).rank(axis=1, pct=True)
b = rolling_rank(vwap_af, 3)
c = rolling_rank(vvolume_af, 18)
y = decay_linear(rolling_corr(b, c, 6), 2).rank(axis=1, pct=True)
ALPHA072 = x / y

# alpha073:
# (max(rank(decay_linear(delta(vwap, 4.72775), 2.91864)),Ts_Rank(decay_linear(((delta(((open * 0.147155) + (low * (1 - 0.147155))), 2.03608) / ((open *0.147155) + (low * (1 - 0.147155)))) * -1), 3.33829), 16.7411)) * -1)
a = vwap_af - vwap_af.shift(4)
x = decay_linear(a, 2).rank(axis=1, pct=True)
b = vopen_af * 0.147155 + vlow_af * (1 - 0.147155)
y = rolling_rank(decay_linear(b.shift(2) / b - 1, 3), 16).reindex(x.index)
ALPHA073 = -x.where(x > y, y)

# alpha074:
# ((rank(correlation(close, sum(adv30, 37.4843), 15.1365)) <rank(correlation(rank(((high * 0.0261661) + (vwap * (1 - 0.0261661)))), rank(volume), 11.4791)))* -1)
adv30 = vvolume_af.rolling(30).mean()
a = adv30.rolling(37).sum()
x = rolling_corr(vclose_af, a, 15).rank(axis=1, pct=True)
b = (vhigh_af * 0.0261661 + vwap_af * (1 - 0.0261661)).rank(axis=1, pct=True)
c = vvolume_af.rank(axis=1, pct=True)
y = rolling_corr(b, c, 11).rank(axis=1, pct=True)
ALPHA074 = -1 * (x < y).where(~x.isna())

# alpha075:
# (rank(correlation(vwap, volume, 4.24304)) < rank(correlation(rank(low), rank(adv50),12.4413)))
adv50 = vvolume_af.rolling(50).mean()
a = rolling_corr(vwap_af, vvolume_af, 4)
x = a.rank(axis=1, pct=True)
b = vlow_af.rank(axis=1, pct=True)
c = adv50.rank(axis=1, pct=True)
y = rolling_corr(b, c, 12).rank(axis=1, pct=True)
ALPHA075 = (x < y).where(~y.isna())

# alpha076:
# alpha077:
# min(rank(decay_linear((((high + low) / 2) - vwap), 20.0451)),rank(decay_linear(correlation(((high + low) / 2), adv40, 3.1614), 5.64125)))
adv40 = vvolume_af.rolling(40).mean()
a = (vhigh_af + vlow_af) / 2 - vwap_af
x = decay_linear(a, 20).rank(axis=1, pct=True)
b = rolling_corr((vhigh_af + vlow_af) / 2, adv40, 3)
y = decay_linear(b, 5).rank(axis=1, pct=True).reindex(x.index)
ALPHA077 = x.where(x < y, y)

# alpha078:
# (rank(correlation(sum(((low * 0.352233) + (vwap * (1 - 0.352233))), 19.7428),sum(adv40, 19.7428), 6.83313))^rank(correlation(rank(vwap), rank(volume), 5.77492)))
adv40 = vvolume_af.rolling(40).mean()
a = vlow_af * 0.352233 + vwap_af * (1 - 0.352233)
b = adv40.rolling(19).sum()
x = rolling_corr(a.rolling(19).sum(), b, 6).rank(axis=1, pct=True)
c = vwap_af.rank(axis=1, pct=True)
d = vvolume_af.rank(axis=1, pct=True)
y = rolling_corr(c, d, 5).rank(axis=1, pct=True)
ALPHA078 = x ** y

# alpha079:
# alpha080:
# alpha081:
# ((rank(Log(product(rank((rank(correlation(vwap, sum(adv10, 49.6054),8.47743))^4)), 14.9655))) < rank(correlation(rank(vwap), rank(volume), 5.07914))) * -1)
adv10 = vvolume_af.rolling(10).mean()
a = adv10.rolling(49).sum()
b = rolling_corr(vwap_af, a, 8).rank(axis=1, pct=True) ** 4
x = np.log(b.rolling(14).apply(np.prod, raw=True)).rank(axis=1, pct=True)
c = vwap_af.rank(axis=1, pct=True)
d = vvolume_af.rank(axis=1, pct=True)
y = rolling_corr(c, d, 5).rank(axis=1, pct=True)
ALPHA081 = -1 * (x < y).where(~x.isna())

# alpha082:
# alpha083:
# ((rank(delay(((high - low) / (sum(close, 5) / 5)), 2)) * rank(rank(volume))) / (((high -low) / (sum(close, 5) / 5)) / (vwap - close)))
a = (vhigh_af - vlow_af) / vclose_af.rolling(5).mean()
b = vvolume_af.rank(axis=1, pct=True)
x = (a * b).rank(axis=1, pct=True)
c = vwap_af - vclose_af
ALPHA083 = (x / (a / c))

# alpha084:
# SignedPower(Ts_Rank((vwap - ts_max(vwap, 15.3217)), 20.7127), delta(close,4.96796))
a = vwap_af - vwap_af.rolling(15).max()
delta = vclose_af - vclose_af.shift(4)
ALPHA084 = rolling_rank(a, 20) ** delta

# alpha086:
# (Ts_Rank(correlation(close, sum(adv20, 14.7444), 6.00049), 20.4195) < rank(((open+ close) - (vwap + open)))) * -1)
adv20 = vvolume_af.rolling(20).mean()
a = adv20.rolling(14).sum()
x = rolling_rank(rolling_corr(vclose_af, a, 6), 20)
y = (vclose_af - vwap_af).rank(axis=1, pct=True).reindex(x.index)
ALPHA086 = -1 * (x < y).where(~x.isna())

# alpha087:


# alpha089:
# alpha090:
# alpha091:
# alpha092:

# alpha093:
# alpha094:
# ((rank((vwap - ts_min(vwap, 11.5783)))^Ts_Rank(correlation(Ts_Rank(vwap,19.6462), Ts_Rank(adv60, 4.02992), 18.0926), 2.70756)) * -1)
adv60 = vvolume_af.rolling(60).mean()
a = vwap_af - vwap_af.rolling(11).min()
b = rolling_rank(vwap_af, 19)
c = rolling_rank(adv60, 4)
x = a.rank(axis=1, pct=True)
y = rolling_rank(rolling_corr(b, c, 18), 2)
ALPHA094 = -x ** y

# alpha096:
# (max(Ts_Rank(decay_linear(correlation(rank(vwap), rank(volume), 3.83878),4.16783), 8.38151), Ts_Rank(decay_linear(Ts_ArgMax(correlation(Ts_Rank(close, 7.45404),Ts_Rank(adv60, 4.13242), 3.65459), 12.6556), 14.0365), 13.4143)) * -1)
# adv60 = vvolume_af.rolling(60).mean()
# a = vwap_af.rank(axis=1, pct=True)
# b = vvolume_af.rank(axis=1, pct=True)
# x = rolling_rank(decay_linear(rolling_corr(a, b, 3), 4), 8)
# c = rolling_rank(vclose_af, 7, percent=False)
# d = rolling_rank(adv60, 4, percent=False)
# e = rolling_corr(c, d, 3).replace(np.nan, 0).rolling(12).apply(np.nanargmax)
# y = rolling_rank(decay_linear(e, 14), 13).reindex(x.index)
# ALPHA096 = -x.where(x > y, y)

# alpha098:
# (rank(decay_linear(correlation(vwap, sum(adv5, 26.4719), 4.58418), 7.18088)) -rank(decay_linear(Ts_Rank(Ts_ArgMin(correlation(rank(open), rank(adv15), 20.8187), 8.62571),6.95668), 8.07206)))
adv5 = vvolume_af.rolling(5).mean()
a = adv5.rolling(26).sum()
x = decay_linear(rolling_corr(vwap_af, a, 4), 7).rank(axis=1, pct=True)
b = vopen_af.rank(axis=1, pct=True)
c = adv5.rank(axis=1, pct=True)
d = rolling_corr(b, c, 20).rolling(8).apply(np.nanargmin)
y = decay_linear(rolling_rank(d, 6), 8).rank(axis=1, pct=True)
ALPHA098 = x - y
print(dt.datetime.now())

#%%
# print(dt.datetime.now())

variable = ' '.join(dir())
talist = re.findall(r'\b[A-Z][0-9A-Z_]*\b', variable)

with pd.option_context('mode.use_inf_as_na', True):
    for indi in talist:
        indi_df = eval(indi)[eval(indi).index > Factor_Latest_Date].stack().rename(indi).reset_index()
        # indi_df = eval(indi)[eval(indi).index == Factor_Latest_Date].stack().rename(indi).reset_index()

        # indi_df.to_csv('%s.csv' % indi, index=False)
        indi_df.to_sql(indi, con, index=False, dtype={'TRADE_DATE': types.DATE}, if_exists='append')
# print(dt.datetime.now())



